from extensions import db
from datetime import datetime


# 数据库ORM创建表单
# 管理员信息表
class Manager(db.Model):
    __tablename__ = "managerinfo"
    id = db.Column(db.Integer, primary_key=True, autoincrement=True)
    managername = db.Column(db.String(100), nullable=False)
    password = db.Column(db.String(100), nullable=False)
    email = db.Column(db.String(100), nullable=True)


# 用户信息表
class User(db.Model):
    __tablename__ = "userinfo"
    id = db.Column(db.Integer, primary_key=True, autoincrement=True)
    username = db.Column(db.String(100), nullable=False)
    password = db.Column(db.String(100), nullable=False)
    studentnum = db.Column(db.String(100), nullable=True)
    email = db.Column(db.String(100), nullable=True)
    status = db.Column(db.Integer, nullable=False, default=0)


# 图书信息表
class Book(db.Model):
    __tablename__ = "bookinfo"
    id = db.Column(db.Integer, primary_key=True, autoincrement=True)
    bookname = db.Column(db.String(20), nullable=False)
    image = db.Column(db.String(1000), nullable=False)
    author = db.Column(db.String(20), nullable=False)
    kind = db.Column(db.String(20), nullable=False)
    description = db.Column(db.String(100), nullable=False)
    press = db.Column(db.String(100), nullable=False)
    date = db.Column(db.String(100), nullable=False)
    inventory = db.Column(db.Integer, nullable=False)


# 用户借书信息表
class Borrow(db.Model):
    __tablename__ = "borrow"
    id = db.Column(db.Integer, primary_key=True, autoincrement=True)
    # 添加用户的外键
    userid = db.Column(db.Integer, db.ForeignKey("userinfo.id", ondelete='CASCADE'))  # 级联删除
    # 创建反向关联，User类中user可以访问borrows属性
    user = db.relationship("User", backref="borrowbooks")
    # 添加书籍的外键
    bookid = db.Column(db.Integer, db.ForeignKey("bookinfo.id", ondelete='CASCADE'))
    # 创建反向关联，Book类中book可以访问borrowusers属性
    book = db.relationship("Book", backref="borrowusers")
    quantity = db.Column(db.Integer, nullable=False)
    status = db.Column(db.Integer, nullable=False, default=0)
    borrowtime = db.Column(db.DateTime, default=datetime.now)
    returntime = db.Column(db.DateTime)


# 用户违规罚款表
class Fine(db.Model):
    __tablename__ = "fine"
    id = db.Column(db.Integer, primary_key=True, autoincrement=True)
    # 添加用户的外键
    userid = db.Column(db.Integer, db.ForeignKey("userinfo.id", ondelete='CASCADE'))
    # 创建反向关联，User类中user可以访问fines属性
    user = db.relationship("User", backref="fines")
    # 添加书籍的外键
    bookid = db.Column(db.Integer, db.ForeignKey("bookinfo.id", ondelete='CASCADE'))
    # 添加borrow的外键
    borrowid = db.Column(db.Integer, db.ForeignKey("borrow.id"))
    # 创建反向关联，Borrow类中borrow可以访问fines属性
    borrow = db.relationship("Borrow", backref="fines")
    money = db.Column(db.Integer, nullable=False)
    status = db.Column(db.Integer, nullable=False, default=0)


# 信息公告，字数不超过255
class Information(db.Model):
    __tablename__ = "information"
    id = db.Column(db.Integer, primary_key=True, autoincrement=True)
    # 添加管理员编号为外键
    managerid = db.Column(db.Integer, db.ForeignKey("managerinfo.id", ondelete='CASCADE'))
    # 创建反向关联，Manager类中manager可以访问borrows属性
    manager = db.relationship("Manager", backref="informations")
    uploadtime = db.Column(db.DateTime, default=datetime.now)
    message = db.Column(db.String(255), nullable=False)


# 在线阅读书籍
class OnlineBook(db.Model):
    __tablename__ = "onlinebook"
    id = db.Column(db.Integer, primary_key=True, autoincrement=True)
    bookname = db.Column(db.String(100), nullable=False)
    author = db.Column(db.String(100), nullable=False)
    kind = db.Column(db.String(100), nullable=False)
    description = db.Column(db.String(100), nullable=False)
    press = db.Column(db.String(100), nullable=False)
    date = db.Column(db.Date, nullable=False)
    text = db.Column(db.Text, nullable=False)


# 用户读书笔记
class BookNote(db.Model):
    __tablename__ = "booknote"
    id = db.Column(db.Integer, primary_key=True, autoincrement=True)
    # 添加用户的外键
    userid = db.Column(db.Integer, db.ForeignKey("userinfo.id", ondelete='CASCADE'))
    # 创建反向关联，User类中user可以访问booknotes属性
    user = db.relationship("User", backref="booknotes")
    # 添加书籍的外键
    bookid = db.Column(db.Integer, db.ForeignKey("bookinfo.id", ondelete='CASCADE'))
    note = db.Column(db.Text, nullable=False)
    notetime = db.Column(db.DateTime, default=datetime.now)


# 用户咨询信件
class UserConsult(db.Model):
    __tablename__ = "userconsult"
    id = db.Column(db.Integer, primary_key=True, autoincrement=True)
    # 添加用户的外键
    userid = db.Column(db.Integer, db.ForeignKey("userinfo.id", ondelete='CASCADE'))
    # 创建反向关联，User类中user可以访问userconsults属性
    user = db.relationship("User", backref="userconsults")
    consult = db.Column(db.Text, nullable=False)
    answer = db.Column(db.Text)
    consulttime = db.Column(db.DateTime, default=datetime.now)


# 图书图片
class BookImage(db.Model):
    __tablename__ = "bookimage"
    id = db.Column(db.Integer, primary_key=True, autoincrement=True)
    # 添加书籍的外键
    bookid = db.Column(db.Integer, db.ForeignKey("bookinfo.id", ondelete='CASCADE'))
    image = db.Column(db.LargeBinary, nullable=False)


# 用户书籍
class UserBook(db.Model):
    __tablename__ = "userbooks"
    id = db.Column(db.Integer, primary_key=True, autoincrement=True)
    # 添加用户的外键
    userid = db.Column(db.Integer, db.ForeignKey("userinfo.id", ondelete='CASCADE'))
    # 创建反向关联，User类中user可以访问userbooks属性
    user = db.relationship("User", backref="userbooks")
    bookname = db.Column(db.String(100), nullable=False)
    author = db.Column(db.String(100), nullable=False)
    kind = db.Column(db.String(100), nullable=False)
    inventory = db.Column(db.Integer, nullable=False)


# 互借图书
class UserBookBorrow(db.Model):
    __tablename__ = "userbookborrow"
    id = db.Column(db.Integer, primary_key=True, autoincrement=True)
    # 添加用户的外键
    userid = db.Column(db.Integer, db.ForeignKey("userinfo.id", ondelete='CASCADE'))
    # 创建反向关联，User类中user可以访问userbooksborrow属性
    user = db.relationship("User", backref="userbooksborrow")
    borrowuserid = db.Column(db.Integer, nullable=True)
    bookname = db.Column(db.String(100), nullable=False)
    author = db.Column(db.String(100), nullable=False)
    kind = db.Column(db.String(100), nullable=False)
    status = db.Column(db.Integer, nullable=False, default=0)
    borrowtime = db.Column(db.DateTime, default=datetime.now)
    returntime = db.Column(db.DateTime)
